import groovy.sql.Sql

class CodeListService  implements java.io.Serializable{
    javax.sql.DataSource dataSource
	
    def List codeSourceList(Integer parameterId) throws Exception {
		def sql = getSql()
		def codeSourceList = []
		try{
			def param = ReportParameter.get(parameterId)
			def sqlStmt = param.parameterValue
			println "sqlStmt "+sqlStmt
		
			sql.eachRow(sqlStmt, {
				Code code = new Code()
				code.setId(it[0].toString())//PKey (first field ) 
				code.setCodeDesc(it[1])// description (second field)
				codeSourceList<<code
				}
			)
		}
		catch(Exception e){
			println "error in CodeListService.codeSourceList"
		}
		return codeSourceList
    }
	
	def List entitySourceList(Integer parameterId,String filterStr, Integer operatorId) throws Exception {
		def sql = getSql()
		def entitySourceList = []
		try{
			def param = ReportParameter.get(parameterId)
			def sqlStmt = param.parameterValue
			def whereClause = " WHERE UPPER(" +param.parameterValue2 +")"
			println "filterStr "+filterStr
			if(filterStr){
				if(operatorId == new Integer(OperatorService.strStartsWith))//starts with
					sqlStmt = sqlStmt + whereClause + " LIKE '" + filterStr.toUpperCase() +"%'"
				else if(operatorId == new Integer(OperatorService.strContains))//contains
					sqlStmt = sqlStmt + whereClause + " LIKE '%" + filterStr.toUpperCase() +"%'"
				else if(operatorId == new Integer(OperatorService.strMatchesExactly))//matches exactly
					sqlStmt = sqlStmt + whereClause + " = '" + filterStr.toUpperCase() +"'"
			}		
			println "added sql " + sqlStmt
			sql.eachRow(sqlStmt, {
				Code code = new Code()
				code.setId(it[0].toString())//PKey
				code.setCodeDesc(it[1])//description or the field you will search by
				entitySourceList<<code
				}
			)
		}
		catch(Exception e){
			println "error in CodeListService.entitySourceList"
		}
        return entitySourceList

    }
	
    private getSql(){
        return new Sql(dataSource)
    }
}